const seq = require('./seq')
const moment = require('moment');
const { QueryTypes } = require('sequelize');
class Template {
  //以下为上报excel操作
  async getexcelfiles(params) //flag:0,获取所有，1,获取本部门 2，获取本人
  {
    const {flag=0,id,pageNum,pageSize} = params
    const start = (pageNum-1)*pageSize
    const limit = pageSize 
    let sqlall=`select dr.* ,ur.realName from datareport dr 
                left join user ur on ur.id=dr.userID 
                where dr.isdelete=0 `
    if(flag==1) {
      sqlall +=` and dr.deptID=${id} `
    }
    if(flag==2) {
      sqlall +=` and dr.userID=${id}  `
    }
    sqlall +=` order by dr.updatedtime desc limit ${start},${limit} `
    const result= await seq.query(sqlall, {type: QueryTypes.SELECT})
    return result
  }

  async getexcelsListCount(params) {
    const {flag=0,id,pageNum,pageSize} = params
    let sqlall=`select count(dr.id) as total from datareport dr 
    left join user ur on ur.id=dr.userID 
    where dr.isdelete=0`
    if(flag==1) {
    sqlall +=` and dr.deptID=${id} `
    }
    if(flag==2) {
    sqlall +=` and dr.userID=${id} `
    }  
    const results = await seq.query(sqlall,
      {
        type: QueryTypes.SELECT
      })
    return results
  }

  async delexcelByID(id)
  {
    const result= await seq.query(`update datareport set isdelete=1 where id=${id}`, {type: QueryTypes.UPDATE})
  }
  async uploadexcel(data,savepath,url,nfilename,ofilename){
    const {templateID=1,deptID,userID,id,tpname=''} =data
    if(id<1){//新增
      const sql = `insert into datareport 
                  set tpname=?,deptID=?,userID=?,savepath=?,url=?,newfilename=?,oldfilename=?,tpid=?`
      const results = await seq.query(sql,
        {
          replacements: [tpname,deptID,userID,savepath,url,nfilename,ofilename,templateID],
          type: QueryTypes.INSERT
        }
      )
      return results
    }else{//更新:先将记录移到历史表中
      
      const sqlmove=`insert into datareportHistory(id,tpName,deptID,userID,savepath,url,newfilename,oldfilename,tpid,createdtime,updatedtime,fileExist)
      select id,tpName,deptID,userID,savepath,url,newfilename,oldfilename,tpid,createdtime,updatedtime,fileExist from datareport  
      where id =${id}`
      await seq.query(sqlmove, {type: QueryTypes.UPDATE})

      const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
      const sql = `update datareport 
                  set savepath=?,url=?,newfilename=?,oldfilename=?,updatedtime=?  
                  where id=${id}`
      const results = await seq.query(sql,
        {
          replacements: [savepath,url,nfilename,ofilename,currentTime],
          type: QueryTypes.UPDATE
        }
      )
      return results
    }
  }
//以下为模版操作:type=0 数据上报模版，1 协同文档模版templateSheet为空
//ispublished:0 协同文档模版发布状态
  async addTemplate(template) {
    //console.log(template)
    const {id,templateName,templateSheet,deptid,userid,isNew,type=0,ispublished=0} = template

    if(id === -1){
      const sql = `insert into template 
                  set templateName=?,templateSheet=?,deptid=?,userid=?,type=?,ispublished=?`
    
      const results = await seq.query(sql,
        {
          replacements: [templateName,templateSheet,deptid,userid,type,ispublished],
          type: QueryTypes.INSERT
        }
      )
      return results
    }else{
      const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
      const sql = `update template 
                  set templateName=?,templateSheet=?,deptid=?,userid=?,updatedtime=?, type=?,ispublished=?
                  where id=${id}`
      const results = await seq.query(sql,
        {
          replacements: [templateName,templateSheet,deptid,userid,currentTime,type,ispublished],
          type: QueryTypes.UPDATE
        }
      )
      return results
    } 
  }

  //ID<>1, id=1,为默认模版，数据上报文件为从本地导入的文件，不在存到数据库，直接存入到目录里
  async getAllTemplate(type=0){
    const sql = `select t.id,t.templateName,dept.name as deptName,u.userName as userName,t.createdtime 
                  from template t 
                  left join department dept on t.deptid=dept.id 
                  left join user u on t.userid=u.id
                  where t.isdelete=0 and t.id<>1 and t.type=${type}`
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return results
  }

  async getAllTemplateByID(id){
    const sql = `select  *  from template  where isdelete=0 and id=${id} `
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }
    )
    return results
  }

  async deleteByID(id){
    const sql = `update template set isdelete=1  where id=${id} `
    const results = await seq.query(sql,
      {
        type: QueryTypes.UPDATE
      }
    )
    return results
  }
}



module.exports = new Template()